<?php

namespace Modules\Api\Controllers;

use Lib\Medoo;
use Lib\Response;
use Lib\Consts;
use Lib\Tool;
use Models\Api\DsCustomApi;
use Models\Api\DsCustomApiVersion;
use Models\Api\DsDatabase;
use Phalcon\Paginator\Adapter\QueryBuilder;

class CustomapiController extends ControllerBase {
    public function listAction() {
        $page = $this->json("page", null,1);
        $limit  = 20;
        $where  = 'api_status!="已删除"';
        $mapper = new DsCustomApi();

//        { "id": "1", "name": "测试数据库", "type": "mysql", "ip": "rm-bp1311o2xle56fuom5m.mysql.rds.aliyuncs.com", "port": "3306", "username": "lvsong",
// "password": "!KQnpjLdyLG^2k9b", "dbname": "dev_bigdata", "parameter": null, "remark": null, "status": "1", "create_time": null, "update_time": null } ] }

        $builder = $mapper->getModelsManager();
        $builder = $builder->createBuilder();
        $builder->where($where);
        $builder->columns(["id","name","request_method","route","server_status","api_status","create_time"]);
        $builder->from('Models\Api\DsCustomApi');
        $builder->orderBy('id DESC');

        $paginator = new QueryBuilder([
            'builder' => $builder,
            'limit'   => $limit,
            'page'    => $page,
        ]);

        $pageData = $paginator->getPaginate();
//        $data     = $pageData->items->toArray();
        return Response::sucmsg($pageData);

    }


    // {"id": "1", "name": "测试数据库", "request_method": "POST", "route": "/xxx/xxx/xxx", "api_status": "正常", "scene": "使用场景","classify": "接口分类", "sql": "select * from aaa","success_return": null, "error_return": null, "input_parameter": [{"field_name":"name1","position":"1","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"},{"field_name":"name2","position":"2","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"}], "output_parameter": [{"field_name":"name1","field_type":"string","remark":"hahaha","description":"xxxx"},{"field_name":"name2","field_type":"string","remark":"hahaha","description":"xxxx"}], "database_id": "1", "create_time": "2023-10-20 16:18:17"}
    public function addAction() {
        $name = $this->json("name", "trim");
        $requestMethod = $this->json("request_method", "trim");
        $route = $this->json("route", "trim");
//        $serverStatus = $this->json("server_status", "trim");
        $serverStatus = "未测试";
        $apiStatus = $this->json("api_status", "trim");

        $scene = $this->json("scene", "trim");
        $classify = $this->json("classify", "trim");
        $sql = $this->json("sql", "trim");
        $successReturn = $this->json("success_return", "trim");
        $errorReturn = $this->json("error_return", "trim");
        // [{"field_name":"name1","position":"1","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"},{"field_name":"name2","position":"2","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"}]
        $inputParameter = $this->json("input_parameter", "trim");
        // [{"field_name":"name1","field_type":"string","remark":"hahaha","description":"xxxx"},{"field_name":"name2","field_type":"string","remark":"hahaha","description":"xxxx"}]
        $outputParameter = $this->json("output_parameter", "trim");
        $databaseId = $this->json("database_id", "trim");
        $dbname= $this->json("dbname", "trim");

        $db = \Phalcon\Di::getDefault()->get('db');
        $db->begin();
        $mapper = new DsCustomApi();
        $mapper->setName($name);
        $mapper->setRequestMethod($requestMethod);
        $mapper->setRoute($route);
        $mapper->setServerStatus($serverStatus);
        $mapper->setApiStatus($apiStatus);
        $mapper->setScene($scene);
        $mapper->setClassify($classify);
        $mapper->setSql(Tool::unicodeDecode($sql));
        $mapper->setSuccessReturn($successReturn);
        $mapper->setErrorReturn($errorReturn);
        $mapper->setInputParameter(json_encode($inputParameter,JSON_UNESCAPED_UNICODE));
        $mapper->setOutputParameter(json_encode($outputParameter,JSON_UNESCAPED_UNICODE));
        $mapper->setDatabaseId($databaseId);
        $mapper->setCreateTime(date("Y-m-d H:i:s"));

        $errMsg = $this->validate($mapper);

        if($errMsg !== false) {
            return Response::errmsg($errMsg, 10010);
        }

        #@todo 判断database_id与数据库名是否一致
//        $dbname= $this->json("dbname", "trim"); $databaseId

        try {
            $mapper->create();
//            return Response::sucmsg([],"添加成功");
        } catch(\Exception $exc) {
            $db->rollback();
            return Response::errmsg($exc->getMessage());
        }

        $versionMapper = new DsCustomApiVersion();
        $versionMapper->setName($name);
        $versionMapper->setRequestMethod($requestMethod);
        $versionMapper->setRoute($route);
        $versionMapper->setServerStatus($serverStatus);
        $versionMapper->setApiStatus($apiStatus);
        $versionMapper->setScene($scene);
        $versionMapper->setClassify($classify);
        $versionMapper->setSql(Tool::unicodeDecode($sql));
        $versionMapper->setSuccessReturn($successReturn);
        $versionMapper->setErrorReturn($errorReturn);
        $versionMapper->setInputParameter(json_encode($inputParameter,JSON_UNESCAPED_UNICODE));
        $versionMapper->setOutputParameter(json_encode($outputParameter,JSON_UNESCAPED_UNICODE));
        $versionMapper->setDatabaseId($databaseId);
        $versionMapper->setCreateTime(date("Y-m-d H:i:s"));
        $versionMapper->setVersion("1");
        $versionMapper->setAddress(Tool::createOrderNo());

//        $errMsg = $this->validate($mapper);
//        if($errMsg !== false) {
//            return Response::errmsg($errMsg, 10010);
//        }

        try {
            $versionMapper->create();
            $db->commit();
            return Response::sucmsg([],"添加成功");
        } catch(\Exception $exc) {
            $db->rollback();
            return Response::errmsg($exc->getMessage());
        }
    }

    private function validate(DsCustomApi $mapper) {
        if(empty($mapper->getName())) {
            return '名称不能为空';
        }

        if(empty($mapper->getRequestMethod())) {
            return '接口请求不能为空';
        }

        if(empty($mapper->getRoute())) {
            return '请求地址不能为空';
        }

        if(empty($mapper->getServerStatus())) {
            return '服务状态不能为空';
        }

        if(empty($mapper->getApiStatus())) {
            return '接口状态不能为空';
        }

//        $mapper->setClassify($classify);
//        $mapper->setSuccessReturn($successReturn);
//        $mapper->setErrorReturn($errorReturn);
//        $mapper->setInputParameter($inputParameter);
//        $mapper->setOutputParameter($outputParameter);
//        $mapper->setOutputParameter($outputParameter);
//        $mapper->setDatabaseId($database_id);

        if(empty($mapper->getScene())) {
            return '使用场景不能为空';
        }

        if(empty($mapper->getSql())) {
            return 'sql不能为空';
        }

        if($mapper->getApiStatus() != "正常" && $mapper->getApiStatus() != "禁用") {
            return '状态信息异常';
        }

        return false;
    }

    public function getinfoAction() {
        $id = $this->json("id", "trim");

        if(empty($id)) {
            return Response::errmsg("id不能为空", 10013);
        }

        $mapper = new DsCustomApi();
        $model  = $mapper::findFirst('id=' . $id);

        if (!$model instanceof DsCustomApi) {
            return Response::errmsg('无此数据');
        }

//        if ($model->getStatus() == Consts::USER_DELETE) {
//            return Response::errmsg('此数据已删除');
//        }

        return Response::sucmsg($model->toArray());
    }

    public function editAction() {
        $id = $this->json("id", "trim");

        if(empty($id)) {
            return Response::errmsg("id不能为空", 10012);
        }

        $name = $this->json("name", "trim");
        $requestMethod = $this->json("request_method", "trim");
        $route = $this->json("route", "trim");
//        $serverStatus = $this->json("server_status", "trim");
        $serverStatus = "未测试";
        $apiStatus = $this->json("api_status", "trim");

        $scene = $this->json("scene", "trim");
        $classify = $this->json("classify", "trim");
        $sql = $this->json("sql", "trim");
        $successReturn = $this->json("success_return", "trim");
        $errorReturn = $this->json("error_return", "trim");
        // [{"field_name":"name1","position":"1","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"},{"field_name":"name2","position":"2","field_type":"string","is_required":"是","default_value":"xxxx","remark":"hahaha"}]
        $inputParameter = $this->json("input_parameter", "trim");
        // [{"field_name":"name1","field_type":"string","remark":"hahaha","description":"xxxx"},{"field_name":"name2","field_type":"string","remark":"hahaha","description":"xxxx"}]
        $outputParameter = $this->json("output_parameter", "trim");
        $databaseId = $this->json("database_id", "trim");
        $dbname= $this->json("dbname", "trim");

        $mapper = new DsCustomApi();
        $model = $mapper->findFirst("id= $id AND api_status!='已删除'");

        if(!$model) {
            return Response::errmsg("无此数据", 10013);
        }

//        var_dump($model->toArray());die();
        $model->setName($name);
        $model->setRequestMethod($requestMethod);
        $model->setRoute($route);
        $model->setServerStatus($serverStatus);
        $model->setApiStatus($apiStatus);
        $model->setScene($scene);
        $model->setClassify($classify);
        $model->setSql(Tool::unicodeDecode($sql));
        $model->setSuccessReturn($successReturn);
        $model->setErrorReturn($errorReturn);
        $model->setInputParameter(json_encode($inputParameter,JSON_UNESCAPED_UNICODE));
        $model->setOutputParameter(json_encode($outputParameter,JSON_UNESCAPED_UNICODE));
        $model->setDatabaseId($databaseId);
        $model->setUpdateTime(date("Y-m-d H:i:s"));

        $errMsg = $this->validate($model);

        if($errMsg !== false) {
            return Response::errmsg($errMsg, 10012);
        }

        try {
            if ($model->update() === false) {
                return Response::errmsg('修改失败');
            }

            return Response::sucmsg([],"修改成功");
        } catch (\Exception $exc) {
            return Response::errmsg($exc->getMessage());
        }
    }

    public function delAction() {
        $id = $this->json("id", "trim");

        if(empty($id)) {
            return Response::errmsg("id不能为空", 10012);
        }

        $mapper = new DsCustomApi();
        $model  = $mapper::findFirst('id=' . $id);

        if (!$model instanceof DsCustomApi) {
            return Response::errmsg('无此数据');
        }

        if ($model->getApiStatus() == "已删除") {
            return Response::errmsg('此数据已删除');
        }

        try {
            $model->setUpdateTime(date("Y-m-d H:i:s"));
            $model->setApiStatus("已删除");
            $model->save();
            return Response::errmsg('删除成功');
        } catch (\Exception $e) {
            return Response::errmsg($e->getMessage());
        }
    }


    public function executeAction() {
        $sql = Tool::unicodeDecode($this->json("sql", "trim"));
        $databaseId = $this->json("database_id", "trim");
        $dbname= $this->json("dbname", "trim");

        // @todo 判断数据库基本信息是否正确
//        if($errMsg !== false) {
//            return Response::errmsg($errMsg, 10010);
//        }

        $mapper = new DsDatabase();
        $model  = $mapper::findFirst("id=$databaseId and status='正常' and dbname='$dbname'");

        if (!$model) {
            return Response::errmsg("数据库信息异常", 10015);
        }

        $type   = $model->getType();
        $dbname = $model->getDbname();
        $ip     = $model->getIp();
        $port   = $model->getPort();
        $username = $model->getUsername();
        $password = $model->getPassword();

        $database = new Medoo([
            'database_type' => $type,
            'database_name' => $dbname,
            'server' => $ip,
            'port' => $port,
            'username' => $username,
            'password' => $password,
            'charset' => 'utf8'
        ]);

        //@todo 添加数据库连接测试至log

        if (!empty($database->errmsg)) {
            return Response::errmsg($database->errmsg, 10016);
        }

        $data = $database->query("$sql")->fetchAll(\PDO::FETCH_ASSOC);

        return Response::sucmsg($data,"连接成功");
    }

    public function openAction() {
        $request = $this->request;
        // post方式
        /*
        $jsonData = $request->getJsonRawBody(true);
        if (!!$jsonData) {
            $sqlPara = "";
            foreach ($jsonData as $key => $value) {
                if(is_string($value)) {
                    $sqlPara .= $key . "=" . "'" . $value . "'";
                } else {
                    $sqlPara .= $key . "=" . $value;
                }

                $sqlPara = $sqlPara . "and" ;
            }

            $sqlPara = trim($sqlPara, "and");
        }
        */



        //========================================================
        $url = $_GET["_url"];

        if (strpos($url,"/api/customapi/open/") === false) {
            return Response::errmsg("信息异常，请联系管理员", 10021);
        }

        $address = substr($url,20);

        // get方式
        $getData = $_GET;

        if (!!$getData) {
            $sqlPara = "";
            foreach ($getData as $key => $value) {
                if($key==="_url") {
                    continue;
                }
//var_dump($key);die();
                if(is_string($value)) {
                    $sqlPara .= $key . "=" . "'" . $value . "'";
                } else {
                    $sqlPara .= $key . "=" . $value;
                }

                $sqlPara = $sqlPara . " and " ;
            }

            $sqlPara = rtrim($sqlPara, " and ");
        }

        if (strlen($address)!== 32) {
            return Response::errmsg("信息异常，请联系管理员", 10022);
        }

        $mapper = new DsCustomApiVersion();
        $model  = $mapper::findFirst("address='$address' and server_status='已测试' and api_status='正常'");

        if (!$model) {
            return Response::errmsg("信息异常，请联系管理员", 10023);
        }

        $sql = $model->getSql();
        // 替换参数
        if (!empty($sqlPara)) {
            $sql = str_replace("@@@", $sqlPara, $sql);
        }


//        var_dump($sql);die();
//        $type   = $model->getType();
//        $dbname = $model->getDbname();
//        $ip     = $model->getIp();
//        $port   = $model->getPort();
//        $username = $model->getUsername();
//        $password = $model->getPassword();
//        $database = new Medoo([
//            'database_type' => $type,
//            'database_name' => $dbname,
//            'server' => $ip,
//            'port' => $port,
//            'username' => $username,
//            'password' => $password,
//            'charset' => 'utf8'
//        ]);

        $data = $this->executeSql($sql,$model->getDatabaseId());

        if ($data["status"] === false) {
            return Response::errmsg($data["msg"], 10029);
        } else {
            return Response::sucmsg($data["msg"],"连接成功");
        }

//        //@todo 添加数据库连接测试至log
//        if (!empty($database->errmsg)) {
//            return Response::errmsg($database->errmsg, 10016);
//        }
//        $data = $database->query("$sql")->fetchAll(\PDO::FETCH_ASSOC);

//        return Response::sqlSuccessMsg($data,"连接成功");
    }


    public function executeSql($sql, $databaseId, $dbname="") {
        // sql未加密的话，暂时不处理
//        $sql        = Tool::unicodeDecode(trim($sql));

        $databaseId = trim($databaseId);
//        $dbname     = trim($dbname);
        // @todo 判断数据库基本信息是否正确
//        if($errMsg !== false) {
//            return Response::errmsg($errMsg, 10010);
//        }
        $mapper = new DsDatabase();
//        $model  = $mapper::findFirst("id=$databaseId and status='正常' and dbname=$dbname");
        $model  = $mapper::findFirst("id=$databaseId and status='正常'");

        if (!$model) {
//            return Response::errmsg("数据库信息异常", 10015);
            return ["status" => false, "msg" => "数据库信息异常"];
        }

        $type   = $model->getType();
        $dbname = $model->getDbname();
        $ip     = $model->getIp();
        $port   = $model->getPort();
        $username = $model->getUsername();
        $password = $model->getPassword();

        $database = new Medoo([
            'database_type' => $type,
            'database_name' => $dbname,
            'server' => $ip,
            'port' => $port,
            'username' => $username,
            'password' => $password,
            'charset' => 'utf8'
        ]);

        //@todo 添加数据库连接测试至log

        if (!empty($database->errmsg)) {
//            return Response::errmsg($database->errmsg, 10016);
            return ["status" => false, "msg" => $database->errmsg];
        }

        $data = $database->query($sql)->fetchAll(\PDO::FETCH_ASSOC);
//        return Response::sucmsg($data[0]["jsondata"],"连接成功");
        // ①这边sql要把字段名称改成jsondata
        // ②json字符要进行替换 \"->"  "{->{   "}->}
//        var_dump($data);die();

        if(empty($data)) {
            return ["status" => false, "msg" => "返回数据为空"];
        }
        $data        = Tool::sqlJsonHandle($data[0]["jsondata"]);
        // 需要判断是否是个json,如何不是，返回报错
//        var_dump($data);die();
        return ["status" => true, "msg" => json_decode($data)];
//        return json_decode($data);
    }
}